import pandas as pd
import numpy as np
import os
data=pd.read_excel('流通股20200326.xlsx')
position=list(data['流通量(万股)'])
stocks=list(data['代码'])
names=list(data['证券名称'])
mv=list(data['最新市值'])
holdList=[]
ratio=1/3
for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
        
    code=int(stocks[i])
    pos=position[i]*10000
    if (pos<=100):
        continue
    codeStr=str(code).rjust(6,'0')
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
    else:
        continue
    
    holdList.append({'code':codeStr,'name':names[i],'hold':pos,'mv':mv[i]*10000,'price':np.round(mv[i]*10000/pos,2)})
stocksTotal=pd.DataFrame(holdList)
stocksTotal['hold']=(stocksTotal['hold'].apply(lambda x:np.floor(x*ratio/100)*100)).astype(int)
stocksTotal['mv']=stocksTotal['hold']*stocksTotal['price']
print(stocksTotal['mv'].sum())
print(stocksTotal)

filename=os.path.join('stocksOf312.h5')
with pd.HDFStore(filename,'a',complib='blosc:zstd',append=False,complevel=9) as store:
    store.append('data',stocksTotal,append=False,format="table",data_columns=data.columns)